import os
import json
import ConfigParser
from lib import constants as C
from lib.db import mysql
from lib.utils.display import Display
from lib.data.ansible_data import Format

display = Display()


class tables(object):
    def __init__(self):
        self.conn = mysql().connect()

    def sqlExecute(self, sql):
        cur = self.conn.cursor()
        try:
            cur.execute(sql)
            self.conn.commit()
            status = 'success'
        except Exception as e:
            self.conn.rollback()
            status = 'failed'
            display.error('sqlExecute Error: %s, %s' % (sql, e))
        finally:
            self.conn.close()
            return status

    def sqlQuery(self, sql):
        cur = self.conn.cursor()
        try:
            cur.execute(sql)
            counts = cur.rowcount
        except Exception as e:
            display.error('sqlQuery Error: %s, %s' % (sql, e))
        finally:
            self.conn.close()
            return counts


class cmdb_hosts(tables):
    def Insert(self, host_data):
        """ Insert Table: cmdb_hosts """

        if not host_data.get('IP'):
             return

        core_facts = host_data.get('data').get('core_facts')

        insert_sql = "\
        insert into cmdb_hosts \
        (\
        IP,\
        hostname,\
        gateway,\
        mac,\
        system,\
        platform,\
        distribution,\
        distribution_version,\
        architecture,\
        kernel,\
        processor,\
        processor_cores,\
        processor_count,\
        vcpus,\
        memory,\
        default_ipv4_mtu,\
        python_version,\
        mysql_version,\
        jboss_version,\
        sqlplus_version,\
        unreachable\
        ) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')\
        " % (
          host_data.get('IP'),
          core_facts.get("hostname"),
          core_facts.get('gateway'),
          core_facts.get('mac'),
          core_facts.get('system'),
          core_facts.get('platform'),
          core_facts.get('distribution'),
          core_facts.get('distribution_version'),
          core_facts.get('architecture'),
          core_facts.get('kernel'),
          core_facts.get('processor'),
          core_facts.get('processor_cores'),
          core_facts.get('processor_count'),
          core_facts.get('vcpus'),
          core_facts.get('memory'),
          core_facts.get('default_ipv4_mtu'),
          core_facts.get('python_version'),
          core_facts.get('mysql_version'),
          core_facts.get('jboss_version'),
          core_facts.get('sqlplus_version'),
          host_data.get('unreachable'),
        )

        return self.sqlExecute(insert_sql)

    def Update(self, host_data):
        """ Update Table: cmdb_hosts"""

        if not host_data.get("IP"):
            return

        core_facts = host_data.get('data').get('core_facts')

        update_sql = "\
        update cmdb_hosts set \
        IP = '%s',\
        hostname = '%s',\
        gateway = '%s',\
        mac = '%s',\
        system = '%s',\
        platform = '%s',\
        distribution = '%s',\
        distribution_version = '%s',\
        architecture = '%s',kernel = '%s',\
        processor = '%s',\
        processor_cores = '%s',\
        processor_count = '%s',\
        vcpus= '%s',\
        memory = '%s',\
        default_ipv4_mtu = '%s',\
        python_version = '%s',\
        mysql_version = '%s',\
        jboss_version = '%s',\
        sqlplus_version = '%s',\
        unreachable = '%s'\
        where IP = '%s'\
        " % (
          host_data.get('IP'),
          core_facts.get('hostname'),
          core_facts.get('gateway'),
          core_facts.get('mac'),
          core_facts.get('system'),
          core_facts.get('platform'),
          core_facts.get('distribution'),
          core_facts.get('distribution_version'),
          core_facts.get('architecture'),
          core_facts.get('kernel'),
          core_facts.get('processor'),
          core_facts.get('processor_cores'),
          core_facts.get('processor_count'),
          core_facts.get('vcpus'),
          core_facts.get('memory'),
          core_facts.get('default_ipv4_mtu'),
          core_facts.get('python_version'),
          core_facts.get('mysql_version'),
          core_facts.get('jboss_version'),
          core_facts.get('sqlplus_version'),
          host_data.get('unreachable'),
          host_data.get('IP')
        )

        return self.sqlExecute(update_sql)

    def Query(self, host_data):
        """ Query Table: cmdb_hosts"""

        if not host_data.get('IP'):
            return

        query_sql = "select * from cmdb_hosts where IP = '%s'" % host_data.get('IP')
        return tables().sqlQuery(query_sql)

    def Test(self):
        """ For Test """

        dicTestResults = host_data()
        for field_name in self.fields_li:
            query_sql = "select * from cmdb_hosts where '%s' != 'None'" % field_name
            sql_counts = int(tables().sqlQuery(query_sql))
            dicTestResults[field_name] = sql_counts

        sql_counts = int(tables().sqlQuery("select * from cmdb_hosts where system = 'Linux'"))
        dicTestResults['Linux'] = sql_counts

        sql_counts = int(tables().sqlQuery("select * from cmdb_hosts where system = 'HP-UX'"))
        dicTestResults['HP-UX'] = sql_counts

        sql_counts = int(tables().sqlQuery("select * from cmdb_hosts where unreachable = 'True'"))
        dicTestResults['Abnormal'] = sql_counts

        sql_counts = int(tables().sqlQuery("select * from cmdb_hosts where unreachable = 'False'"))
        dicTestResults['Normal'] = sql_counts

        return json.dumps(dicTestResults, indent=4)


class cmdb_users(tables):
    def Insert(self, host_data):
        """ Insert Table: cmdb_users"""

        if not host_data.get("IP") or not host_data.get("user_name"):
            return

        insert_sql = "\
        insert into cmdb_users \
        (\
        IP,\
        username,\
        uid,\
        gid,\
        userhome,\
        usershell,\
        usertype\
        ) values ('%s','%s','%s','%s','%s','%s','%s')\
        " % (
          host_data.get("IP"),
          host_data.get("user_name"),
          host_data.get("user_uid"),
          host_data.get("user_gid"),
          host_data.get("user_home"),
          host_data.get("user_shell"),
          host_data.get("user_type")
        )

        return self.sqlExecute(insert_sql)

    def Update(self, host_data):
        """ Update Table: cmdb_users"""

        if not host_data.get("IP") or not host_data.get("user_name"):
            return

        update_sql = "\
        update cmdb_users set \
        username = '%s',\
        uid = '%s',\
        gid = '%s',\
        userhome = '%s',\
        usershell = '%s'\
        usertype = '%s'\
        where IP = '%s'\
        and username = '%s'\
        " % (
          host_data.get("user_name"),
          host_data.get("user_uid"),
          host_data.get("user_gid"),
          host_data.get("user_home"),
          host_data.get("user_shell"),
          host_data.get("user_type"),
          host_data.get("IP"),
          host_data.get("user_name")
        )

        return self.sqlExecute(update_sql)

    def Query(self, host_data):
        """ Query Table: cmdb_users"""

        if not host_data.get("IP"):
            return

        query_sql = "select * from cmdb_users where IP = '%s' and username = '%s'" % (host_data.get("IP"), host_data.get("user_name"))
        return self.sqlQuery(query_sql)

def main():
    test_hosts = cmdb_hosts()


if __name__ == '__main__':
    main()
